const { Sequelize } = require('sequelize');
const config = require('./config');
const logger = require('./logger');

// 创建Sequelize配置对象
const sequelizeConfig = {
  host: config.database.host,
  port: config.database.port,
  dialect: config.database.dialect,
  logging: config.database.logging,
  pool: config.database.pool,
  define: config.database.define,
};

// 添加存储路径（SQLite）
if (config.database.storage) {
  sequelizeConfig.storage = config.database.storage;
}

// 添加时区（非SQLite）
if (config.database.timezone) {
  sequelizeConfig.timezone = config.database.timezone;
}

// 创建Sequelize实例
const sequelize = new Sequelize(
  config.database.database,
  config.database.username,
  config.database.password,
  {
    ...sequelizeConfig,
    
    // 查询配置
    query: {
      raw: false,
      nest: false
    },
    
    // 连接配置
    dialectOptions: {
      charset: 'utf8mb4',
      collate: 'utf8mb4_unicode_ci',
      supportBigNumbers: true,
      bigNumberStrings: true,
      dateStrings: true,
      typeCast: function (field, next) {
        // 处理TINYINT(1)为布尔值
        if (field.type === 'TINY' && field.length === 1) {
          return (field.string() === '1');
        }
        return next();
      }
    },
    
    // 重试配置
    retry: {
      match: [
        /ETIMEDOUT/,
        /EHOSTUNREACH/,
        /ECONNRESET/,
        /ECONNREFUSED/,
        /TIMEOUT/,
        /ESOCKETTIMEDOUT/,
        /EHOSTUNREACH/,
        /EPIPE/,
        /EAI_AGAIN/,
        /SequelizeConnectionError/,
        /SequelizeConnectionRefusedError/,
        /SequelizeHostNotFoundError/,
        /SequelizeHostNotReachableError/,
        /SequelizeInvalidConnectionError/,
        /SequelizeConnectionTimedOutError/
      ],
      max: 3
    },
    
    // 基准测试
    benchmark: config.env === 'development',
    
    // 事务隔离级别
    isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED,
    
    // 钩子
    hooks: {
      beforeConnect: (config) => {
        logger.debug('正在连接数据库...', {
          host: config.host,
          port: config.port,
          database: config.database
        });
      },
      afterConnect: (connection, config) => {
        logger.info('数据库连接成功', {
          host: config.host,
          port: config.port,
          database: config.database
        });
      },
      beforeDisconnect: (connection) => {
        logger.info('正在断开数据库连接...');
      },
      afterDisconnect: (connection) => {
        logger.info('数据库连接已断开');
      }
    }
  }
);

// 测试数据库连接
const testConnection = async () => {
  try {
    await sequelize.authenticate();
    logger.info('数据库连接测试成功');
    return true;
  } catch (error) {
    logger.error('数据库连接测试失败:', error);
    return false;
  }
};

// 同步数据库模型
const syncDatabase = async (options = {}) => {
  try {
    const defaultOptions = {
      force: false,
      alter: config.env === 'development',
      logging: config.database.logging
    };
    
    const syncOptions = { ...defaultOptions, ...options };
    
    logger.info('开始同步数据库模型...', syncOptions);
    
    await sequelize.sync(syncOptions);
    
    logger.info('数据库模型同步完成');
    return true;
  } catch (error) {
    logger.error('数据库模型同步失败:', error);
    throw error;
  }
};

// 关闭数据库连接
const closeConnection = async () => {
  try {
    await sequelize.close();
    logger.info('数据库连接已关闭');
  } catch (error) {
    logger.error('关闭数据库连接时出错:', error);
    throw error;
  }
};

// 执行原生SQL查询
const executeQuery = async (sql, options = {}) => {
  try {
    const defaultOptions = {
      type: Sequelize.QueryTypes.SELECT,
      logging: config.database.logging
    };
    
    const queryOptions = { ...defaultOptions, ...options };
    
    const result = await sequelize.query(sql, queryOptions);
    
    logger.debug('SQL查询执行成功', { sql, rowCount: result.length });
    
    return result;
  } catch (error) {
    logger.error('SQL查询执行失败:', { sql, error: error.message });
    throw error;
  }
};

// 事务处理
const transaction = async (callback, options = {}) => {
  const t = await sequelize.transaction(options);
  
  try {
    const result = await callback(t);
    await t.commit();
    
    logger.debug('事务提交成功');
    
    return result;
  } catch (error) {
    await t.rollback();
    
    logger.error('事务回滚:', error.message);
    
    throw error;
  }
};

// 获取数据库信息
const getDatabaseInfo = async () => {
  try {
    const [results] = await sequelize.query('SELECT VERSION() as version');
    const version = results[0]?.version || 'Unknown';
    
    const [sizeResults] = await sequelize.query(`
      SELECT 
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
      FROM information_schema.tables 
      WHERE table_schema = '${config.database.database}'
    `);
    
    const size = sizeResults[0]?.size_mb || 0;
    
    const [tableResults] = await sequelize.query(`
      SELECT COUNT(*) as table_count
      FROM information_schema.tables 
      WHERE table_schema = '${config.database.database}'
    `);
    
    const tableCount = tableResults[0]?.table_count || 0;
    
    return {
      version,
      size: `${size} MB`,
      tableCount,
      database: config.database.database,
      host: config.database.host,
      port: config.database.port
    };
  } catch (error) {
    logger.error('获取数据库信息失败:', error);
    return null;
  }
};

// 检查数据库健康状态
const checkHealth = async () => {
  try {
    const startTime = Date.now();
    
    await sequelize.authenticate();
    
    const responseTime = Date.now() - startTime;
    
    // 检查连接池状态
    const pool = sequelize.connectionManager.pool;
    const poolInfo = {
      total: pool.size,
      used: pool.used,
      waiting: pool.pending
    };
    
    return {
      status: 'healthy',
      responseTime: `${responseTime}ms`,
      pool: poolInfo,
      timestamp: new Date().toISOString()
    };
  } catch (error) {
    return {
      status: 'unhealthy',
      error: error.message,
      timestamp: new Date().toISOString()
    };
  }
};

// 数据库备份（仅限开发环境）
const backup = async (backupPath) => {
  if (config.env === 'production') {
    throw new Error('生产环境不允许通过此方法备份数据库');
  }
  
  try {
    const { exec } = require('child_process');
    const util = require('util');
    const execAsync = util.promisify(exec);
    
    const command = `mysqldump -h ${config.database.host} -P ${config.database.port} -u ${config.database.username} -p${config.database.password} ${config.database.database} > ${backupPath}`;
    
    await execAsync(command);
    
    logger.info('数据库备份完成', { backupPath });
    
    return true;
  } catch (error) {
    logger.error('数据库备份失败:', error);
    throw error;
  }
};

// 监听连接事件 - 新版本Sequelize不再支持connectionManager事件
// sequelize.connectionManager.on('connect', (connection) => {
//   logger.debug('新的数据库连接已建立');
// });

// sequelize.connectionManager.on('disconnect', (connection) => {
//   logger.debug('数据库连接已断开');
// });

// 导出
module.exports = {
  sequelize,
  Sequelize,
  testConnection,
  syncDatabase,
  closeConnection,
  executeQuery,
  transaction,
  getDatabaseInfo,
  checkHealth,
  backup
};